﻿<?xml version="1.0" encoding="utf-8" ?>
<!--namespace必须用否者就报错（读取配置文件报：未将对象引用设置到对象的实例）-->
<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


  <statements>


    <select id="SearchProduct" parameterClass="Kaiser.Modules.Cubic.ViewModel.ProductSearch" resultClass="HashTable">
      SELECT
      [ProductId]
      ,[ProductCode]
      ,[ProductName]
      ,[ProductSPEC]
      ,[ProductSize]
      ,[ProductDepict] as DehumidifierDescription
      ,ProductPic
      ,[ProductPrice]
      ,[ProductCount]
      ,[ProductDesc]
      ,FileNum
      ,[ProductImgName]
      ,[LastModifyUserName]
      ,[LastModifyDateTime]
      ,[LastModifyUserFullName]
      ,[CreateUserFullName]
      ,[CreateUserName]
      ,[CreateDateTime]
      ,[LabelImgUrl]
      ,[CategoryId]
      ,[CategoryName]
      ,ProductEnglishName
      ,EnglishDehumidifierDescription
      FROM  [Cubic_V_Product] t1
      left join Cubic_ProductCategory t2 on t1.CategoryId = t2.Id
      <dynamic prepend="where">
        <isParameterPresent>
          <isNotEmpty prepend="and" property="CategoryName">
            [CategoryName] like '%$CategoryName$%'
          </isNotEmpty>
          <isNotEmpty prepend="and" property="ProductName">
            [ProductName] like '%$ProductName$%'
          </isNotEmpty>
          <isNotEmpty prepend="and" property="CategoryId">
            CategoryId = '$CategoryId$'
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>
      <dynamic prepend="order by">
        <isPropertyAvailable property="Sort">
          <isEmpty property="Sort">
            LastModifyDateTime desc
          </isEmpty>
          <isNotEmpty property="Sort">
            $Sort$ $Order$
          </isNotEmpty>
        </isPropertyAvailable>
      </dynamic>
    </select>

    <select id="GetProduct" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.ProductDetail">
      select ProductId,
      ProductCode,
      ProductName,
      ProductImgUrl as ProductPic,
      ProductDepict as DehumidifierDescription,
      ProductEnglishName,
      EnglishDehumidifierDescription
      ,FileNum
      ,[CreateUserFullName]
      ,[CreateUserName]
      ,[CreateDateTime]
      ,CategoryId
      ,Features
      from Cubic_V_Product
      where ProductId='$id$'
    </select>

    <select id="GetPhotoByObjectIDOnCubicPhoto" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.Photo">
      SELECT  [Id]
      ,[ProductId]
      ,[PhotoName] as Name
      ,[PhotoUrl] as Url
      ,[PhotoDesc] as Description
      ,[PhotoSequence] as Sequence
      ,[PhotoType] as Type
      FROM [Cubic_Product_Photo]

      WHERE [ProductId] =#id#
    </select>

    <select id="GetPhotoByObjectIDOnCubicFile" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.File">
      SELECT
      Attach_ID as Id,
      Attach_FileName as FileName
      ,Attach_FileType as FileType
      ,Attach_Size as FileSize
      ,Attach_Path as Url
      FROM Attachment
      WHERE [Attach_ObjectID] =#id# and Attach_ObjectModule='CubicProductFile'
    </select>


    <select id="GetProductsByCategoryCode" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.ProductDetail">
      SELECT
      ProductId,
      ProductCode,
      ProductName,
      FileNum,
      (SELECT  top 1 [PhotoUrl] FROM  [Cubic_Product_Photo] where PhotoType='thumbnail' and productid=Cubic_V_Product.productid) as ProductPic,
      EnglishDehumidifierDescription,
      CategoryId,
      Features
      from Cubic_V_Product
      <dynamic prepend="where">
        <isParameterPresent>
          <isNotEmpty prepend="and" >
            CategoryId in (SELECT [id] FROM   [Cubic_ProductCategory] where [id]=#value# or ParentId=#value# )
          </isNotEmpty>
        </isParameterPresent>
      </dynamic>
       order by categoryid asc
    </select>

    <select id="GetProductCateogrys" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.ProductCategory">
      SELECT
      CategoryName,
      CategoryCode
      from Cubic_ProductCategory
      where ParentId='$categoryParentId$'
    </select>

    <select id="GetProductCategory" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.ProductCategory">
      SELECT
      (select top 1 CategoryName from Cubic_ProductCategory as a where a.id=Cubic_ProductCategory.parentid) as ParentName,
      CategoryName,
      CategoryCode
      from Cubic_ProductCategory
      where Id='$categoryId$'
    </select>

    <select id="GetNewsByCategoryCode" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.News.NewsDetail">
      SELECT
      cast(tb1.Id as varchar) as Id,
      CreateTime as PublishDateTime,
      ContentTitle TitleShort,
      ContentDetail as Content,
      Author,
      EnglishTitle,
      EnglishContent
      from CMS_Content as tb1,CMS_CategoryContent as tb2
      where tb2.CategoryId='$categoryId$' and tb2.ContentId=tb1.id
    </select>


    <insert id="InsertProduct" parameterClass="hashtable">
      INSERT INTO [Cubic_Product]
      (
      [ProductId]
      ,ProductCode
      ,[ProductName]
      ,[ProductDepict]
      ,[ProductImgUrl]
      ,[LastModifyUserName]
      ,[LastModifyDateTime]
      ,[LastModifyUserFullName]
      ,[CreateUserFullName]
      ,[CreateUserName]
      ,[CreateDateTime]
      ,ProductEnglishName
      ,EnglishDehumidifierDescription
      ,CategoryId
      ,Features
      )
      VALUES
      (
      #ProductId#
      ,#ProductCode#
      ,#ProductName#
      ,#DehumidifierDescription#
      ,#ProductPic#
      ,#LastModifyUserName#
      ,#LastModifyDateTime#
      ,#LastModifyUserFullName#
      ,#CreateUserFullName#
      ,#CreateUserName#
      ,#CreateDateTime#
      ,#ProductEnglishName#
      ,#EnglishDehumidifierDescription#
      ,#CategoryId#
      ,#Features#
      )
    </insert>
    <update id="UpdateProduct" parameterClass="hashtable" resultClass="int">
      UPDATE [Cubic_Product]
      SET
      [ProductId] =#ProductId#
      ,ProductCode=#ProductCode#
      ,[ProductName]=#ProductName#
      ,[ProductDepict]=#DehumidifierDescription#
      ,[ProductImgUrl]=#ProductPic#
      ,[LastModifyUserName]=#LastModifyUserName#
      ,[LastModifyDateTime]=#LastModifyDateTime#
      ,[LastModifyUserFullName]=#LastModifyUserFullName#
      ,[CreateUserFullName]=#CreateUserFullName#
      ,[CreateUserName]=#CreateUserName#
      ,[CreateDateTime]=#CreateDateTime#
      ,ProductEnglishName=#ProductEnglishName#
      ,EnglishDehumidifierDescription=#EnglishDehumidifierDescription#
      ,CategoryId=#CategoryId#
      ,Features=#Features#
      WHERE  [ProductId] =#ProductId#
    </update>

    <delete id="DeleteProductList" parameterClass="string" resultClass="int">
      DELETE FROM Cubic_Product
      WHERE [ProductId]  in
      <iterate   property="ids" open="(" close=")"  conjunction=",">
        #ids[]#
      </iterate>
    </delete>

    <delete id="DeleteProduct" parameterClass="string" resultClass="int">
      DELETE FROM Cubic_Product
      WHERE [ProductId] ='$value$'
    </delete>


    <insert id="ProductPhoto.Insert" parameterClass="hashtable">
      INSERT INTO [Cubic_Product_Photo]
      (

      [ProductId]
      ,[PhotoName]
      ,[PhotoUrl]
      ,[PhotoDesc]
      ,[PhotoSequence]
      ,[PhotoType]
      )
      VALUES
      (

      #ProductId#
      ,#Name#
      ,#Url#
      ,#Description#
      ,#Sequence#
      ,#Type#
      )
    </insert>

    <update id="ProductPhoto.Update" parameterClass="hashtable" resultClass="int">
      UPDATE [Cubic_Product_Photo]
      SET
    
       [PhotoName] =#Name#
      ,[PhotoUrl] =#Url#
      ,[PhotoDesc] =#Description#
      ,[PhotoSequence] =#Sequence#
      ,[PhotoType]=#Type#
      WHERE  [Id] =#Id#
    </update>

    <select id="ProductPhoto.Get" parameterClass="string" resultClass="Kaiser.Modules.Cubic.DomainModel.Product.Photo">
      select
      [Id]
      ,[ProductId]
      ,[PhotoName] as Name
      ,[PhotoUrl] as Url
      ,[PhotoDesc] as Description
      ,[PhotoSequence] as Sequence
      ,[PhotoType] as Type
      from Cubic_Product_Photo
      where [Id]='$Id$'
    </select>


    <delete id="ProductPhoto.DeleteList" parameterClass="string" resultClass="int">
      delete from [Cubic_Product_Photo] where [Id] =$value$

    </delete>

    <select id="SearchProductPhoto"  resultClass="HashTable">
      SELECT     [Id]
      ,[ProductId]
      ,[PhotoName] as Name
      ,[PhotoUrl] as Url
      ,[PhotoDesc] as Description
      ,[PhotoSequence] as Sequence
      ,[PhotoType] as Type
      FROM  [Cubic_Product_Photo]  where ProductId='$value$'
    </select>
    
  </statements>
</sqlMap>

